{
 "cells": [
  {
   "metadata": {
    "ExecuteTime": {
     "end_time": "2025-04-10T16:31:35.697884Z",
     "start_time": "2025-04-10T16:31:32.701623Z"
    }
   },
   "cell_type": "code",
   "source": [
    "# 安装库\n",
    "!pip install mysql-connector-python -i https://pypi.tuna.tsinghua.edu.cn/simple"
   ],
   "id": "1f7ae00faeb69c1a",
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Looking in indexes: https://pypi.tuna.tsinghua.edu.cn/simple\n",
      "Requirement already satisfied: mysql-connector-python in c:\\users\\brion\\miniconda3\\lib\\site-packages (9.2.0)\n"
     ]
    }
   ],
   "execution_count": 1
  },
  {
   "metadata": {
    "collapsed": true,
    "ExecuteTime": {
     "end_time": "2025-04-10T16:38:41.524921Z",
     "start_time": "2025-04-10T16:35:40.297825Z"
    }
   },
   "cell_type": "code",
   "source": [
    "# 遍历循环每张Excel表格，将数据写入到Mysql中\n",
    "import os\n",
    "import csv\n",
    "import mysql.connector\n",
    "\n",
    "def create_table(cursor, table_name, columns):\n",
    "    columns_def = ', '.join([f\"`{col}` TEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci\" for col in columns])\n",
    "    create_table_query = f\"CREATE TABLE IF NOT EXISTS `{table_name}` ({columns_def})\"\n",
    "    cursor.execute(create_table_query)\n",
    "\n",
    "def insert_data(cursor, connection, table_name, columns, data):\n",
    "    batch_size = 1000  # 每次插入 1000 条记录\n",
    "    for i in range(0, len(data), batch_size):\n",
    "        batch = data[i:i + batch_size]\n",
    "        placeholders = ', '.join(['%s'] * len(columns))\n",
    "        insert_query = f\"INSERT INTO `{table_name}` ({', '.join([f'`{col}`' for col in columns])}) VALUES ({placeholders})\"\n",
    "        cursor.executemany(insert_query, batch)\n",
    "        connection.commit()\n",
    "\n",
    "def import_csv_to_mysql(folder_path, host, user, password, database):\n",
    "    try:\n",
    "        connection = mysql.connector.connect(\n",
    "            host=host,\n",
    "            user=user,\n",
    "            password=password,\n",
    "            database=database,\n",
    "            charset='utf8mb4'  # 指定字符集为 utf8mb4\n",
    "        )\n",
    "        cursor = connection.cursor()\n",
    "\n",
    "        for filename in os.listdir(folder_path):\n",
    "            if filename.endswith('.csv'):\n",
    "                table_name = os.path.splitext(filename)[0]\n",
    "                file_path = os.path.join(folder_path, filename)\n",
    "\n",
    "                with open(file_path, 'r', encoding='utf-8') as csvfile:\n",
    "                    reader = csv.reader(csvfile)\n",
    "                    columns = next(reader)\n",
    "                    data = list(reader)\n",
    "\n",
    "                    create_table(cursor, table_name, columns)\n",
    "                    insert_data(cursor, connection, table_name, columns, data)\n",
    "\n",
    "        print(\"数据导入成功！\")\n",
    "    except mysql.connector.Error as err:\n",
    "        print(f\"发生错误: {err}\")\n",
    "    finally:\n",
    "        if connection.is_connected():\n",
    "            cursor.close()\n",
    "            connection.close()\n",
    "# 设置参数\n",
    "folder_path = 'data'\n",
    "host = 'localhost'\n",
    "user = 'douban'\n",
    "password = 'douban123'\n",
    "database = 'douban'\n",
    "\n",
    "# 调用函数\n",
    "import_csv_to_mysql(folder_path, host, user, password, database)\n"
   ],
   "id": "initial_id",
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "数据导入成功！\n"
     ]
    }
   ],
   "execution_count": 4
  },
  {
   "metadata": {
    "ExecuteTime": {
     "end_time": "2025-04-10T15:54:09.328426Z",
     "start_time": "2025-04-10T15:54:09.324166Z"
    }
   },
   "cell_type": "code",
   "outputs": [],
   "execution_count": 2,
   "source": "# 将Mysql中的表进行多表链接\n",
   "id": "87996312fa108342"
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 2
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython2",
   "version": "2.7.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
